﻿using NPOI.SS.UserModel;
using Fortunate.API.BASE;
using Microsoft.AspNetCore.Mvc;
using NPOI.HSSF.UserModel;
using Fortunate.Common.Global;
using Fortunate.Common.Utility;
using Fortunate.Application.Entities;
using NPOI.XSSF.UserModel;
using Fortunate.ViewModel.ExeclModule;
using Fortunate.Common.Extensions;
using Fortunate.IService;
using AutoMapper;

namespace Fortunate.API.Controllers
{
    /// <summary>
    /// Excel操作
    /// </summary>
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class ExcelController : BaseApiController
    {
        private readonly IWebHostEnvironment _webHostEnvironment;
        private readonly IExeclService _iExeclService;
        protected readonly IMapper _mapper;
        public ExcelController(IWebHostEnvironment webHostEnvironment, IExeclService iExeclService, IMapper mapper)
        {
            _webHostEnvironment = webHostEnvironment;
            _iExeclService = iExeclService;
            _mapper = mapper;
        }

        /// <summary>
        /// 创建一个Execl
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public IActionResult CreateExecl()
        {
            string FilePath = FortunateGlobalContext.DirectoryConfig.GetBusinessAttachmentPath();
            string SaveUrl = Path.Combine(_webHostEnvironment.ContentRootPath, FilePath, "xls\\");
            FileContext.CreateDirectory(SaveUrl);
            string path = SaveUrl + string.Concat(DateTime.Now.ToString("yyyyMMddHHmmss"), ".xls");
            HSSFWorkbook workbook = new HSSFWorkbook();
            FileStream filestream = new FileStream(path, FileMode.Create);
            workbook.CreateSheet("测试 A");
            workbook.CreateSheet("测试 B");
            workbook.CreateSheet("测试 C");
            workbook.Write(filestream);
            workbook.Close();
            filestream.Close();
            filestream.Dispose();
            return Success("创建成功");
        }

        /// <summary>
        /// 向Excel中添加内容
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public IActionResult InsertContext()
        {

            string FilePath = FortunateGlobalContext.DirectoryConfig.GetBusinessAttachmentPath();
            string SaveUrl = Path.Combine(_webHostEnvironment.ContentRootPath, FilePath, "xls\\");
            FileContext.CreateDirectory(SaveUrl);
            string path = SaveUrl + string.Concat(DateTime.Now.ToString("yyyyMMddHHmmss"), ".xls");

            HSSFWorkbook workbook = new HSSFWorkbook();
            FileStream filestream = new FileStream(path, FileMode.Create);

            // 测试。 
            ISheet sheet1 = workbook.CreateSheet("测试 A");

            //依次创建行和列
            for (int i = 0; i < 10; i++)
            {
                IRow row1 = sheet1.CreateRow(i);
                for (int j = 0; j < 10; j++)
                {
                    ICell cell1 = row1.CreateCell(j);
                    cell1.SetCellValue("第" + (i + 1) + "行，第" + (j + 1) + "列");
                }
            }

            workbook.Write(filestream);
            workbook.Close();

            filestream.Close();
            filestream.Dispose();

            return Success("添加成功");
        }

        /// <summary>
        /// 下载导入模板
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public FileResult DownLoadExcelTemplate()
        {
            FileStream file = new FileStream(_webHostEnvironment.ContentRootPath + "\\UploadFiles\\Template\\Template.xls", FileMode.Open, FileAccess.Read);//读入excel模板
            HSSFWorkbook book = new HSSFWorkbook(file);
            System.IO.MemoryStream ms = new MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            string dt = DateTime.Now.ToYear();
            string filename = "导入数据的excel模板" + dt + ".xls";
            return File(ms, "application/vns.ms-excel", filename);
        }

        /// <summary>
        /// 导入Execl
        /// </summary>
        /// <param name="file">文件</param>
        /// <returns></returns>
        [HttpPost]
        public async Task<IActionResult> ImportExecl(IFormFile file)
        {
            try
            {
                string fileExt = Path.GetExtension(file.FileName).ToLower();

                MemoryStream ms = new MemoryStream();
                file.CopyTo(ms);
                ms.Seek(0, SeekOrigin.Begin);
                IWorkbook book;
                if (fileExt == ".xlsx")
                {
                    book = new XSSFWorkbook(ms);
                }
                else if (fileExt == ".xls")
                {
                    book = new HSSFWorkbook(ms);
                }
                else
                {
                    return Fail("文件类型不支持，仅支持【.xls、.xlsx】");
                }
                ISheet sheet = book.GetSheetAt(0);
                int CountRow = sheet.LastRowNum + 1;//获取总行数
                if (CountRow - 1 == 0)
                {
                    return Fail("Excel列表数据项为空");
                }
                var Execls = new List<ExeclList>();
                for (int i = 1; i < CountRow; i++)
                {
                    //实例化实体对象
                    ExeclList execl = new();
                    var row = sheet.GetRow(i);
                    var index = row.LastCellNum;//总列数
                    execl.id = base.GetGuid;
                    execl.Value1 = row.GetCell(0).GetCellValue();
                    execl.Value2 = row.GetCell(1).GetCellValue();
                    execl.Value3 = row.GetCell(2).GetCellValue();
                    execl.Value4 = row.GetCell(3).GetCellValue();
                    execl.Value5 = row.GetCell(4).GetCellValue();
                    execl.Value6 = row.GetCell(5).GetCellValue();
                    execl.Value7 = row.GetCell(6).GetCellValue();
                    execl.Value8 = row.GetCell(7).GetCellValue();
                    execl.Value9 = row.GetCell(8).GetCellValue();
                    execl.Value10 = row.GetCell(9).GetCellValue();
                    Execls.Add(execl);
                }
                await _iExeclService.ImportExcelAsync(_mapper.Map<List<ImportExcel>>(Execls));
                return Success($"数据导入成功,共导入{CountRow - 1}条数据。");
            }
            catch (Exception ex)
            {
                return Fail("服务器异常");
            }
        }

        /// <summary>
        /// 导出Execl
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public async Task<IActionResult> ExportExecl()
        {
            var ExeclEntitiy = await _iExeclService.ExportExeclAsync();
            var ExeclData = _mapper.Map<List<ImportExcel>>(ExeclEntitiy);
            HSSFWorkbook excelBook = new HSSFWorkbook();
            //创建Excel工作表
            ISheet sheet1 = excelBook.CreateSheet("测试导出");
            //给Sheet(故障码信息）添加第一行的头部标题
            IRow row1 = sheet1.CreateRow(0);

            row1.CreateCell(0).SetCellValue("value1");
            row1.CreateCell(1).SetCellValue("value2");
            row1.CreateCell(2).SetCellValue("value3");
            row1.CreateCell(3).SetCellValue("value4");
            row1.CreateCell(4).SetCellValue("value5");
            row1.CreateCell(5).SetCellValue("value6");
            row1.CreateCell(6).SetCellValue("value7");
            row1.CreateCell(7).SetCellValue("value8");
            row1.CreateCell(8).SetCellValue("value9");
            row1.CreateCell(9).SetCellValue("value10");
            //添加数据行：将表格数据逐步写入sheet1各个行中（也就是给每一个单元格赋值）
            for (int i = 0; i < ExeclData.Count; i++)
            {
                //sheet1.CreateRow(i).
                //创建行
                IRow rowTemp = sheet1.CreateRow(i + 1);
                rowTemp.CreateCell(0).SetCellValue(ExeclData[i].value1);
                rowTemp.CreateCell(1).SetCellValue(ExeclData[i].value2);
                rowTemp.CreateCell(2).SetCellValue(ExeclData[i].value3);
                rowTemp.CreateCell(3).SetCellValue(ExeclData[i].value4);
                rowTemp.CreateCell(4).SetCellValue(ExeclData[i].value5);
                rowTemp.CreateCell(5).SetCellValue(ExeclData[i].value6);
                rowTemp.CreateCell(6).SetCellValue(ExeclData[i].value7);
                rowTemp.CreateCell(7).SetCellValue(ExeclData[i].value8);
                rowTemp.CreateCell(8).SetCellValue(ExeclData[i].value9);
                rowTemp.CreateCell(9).SetCellValue(ExeclData[i].value10);
            }
            //输出的文件名称
            string fileName = "导出信息" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-ffff") + ".xls";
            //把Excel转为流，输出
            //创建文件流
            MemoryStream bookStream = new MemoryStream();
            //将工作薄写入文件流
            excelBook.Write(bookStream);
            //输出之前调用Seek（偏移量，游标位置) 把0位置指定为开始位置
            bookStream.Seek(0, System.IO.SeekOrigin.Begin);
            //Stream对象,文件类型,文件名称
            return File(bookStream, "application/vnd.ms-excel", fileName);
        }
    }
}
